      CREATE TABLE IF NOT EXISTS `demo_people` (
       `pid` int(11) NOT NULL AUTO_INCREMENT,
       `name` varchar(22) NOT NULL,
       `phone` varchar(22) NOT NULL,
       PRIMARY KEY (`pid`)
      )

      INSERT INTO `demo_people` (`pid`, `name`, `phone`) VALUES
      (1, 'vikas dwivedi', '09711500404'),
      (2, 'Ravi Shankar', '98262626222'),
      (3, 'hemant Sharma', '29293947474747'),
      (4, 'Madhulika sharma', '9835635353353'),
      (5, 'Vishal Dwivedi', '9363636465475'),
      (6, 'Prakash Dwivedi', '92846464463');

    CREATE TABLE IF NOT EXISTS `demo_property` (
     `spid` int(11) NOT NULL AUTO_INCREMENT,
     `pid` int(11) NOT NULL,
     `selling` varchar(255) NOT NULL,
     PRIMARY KEY (`spid`)
    );

    INSERT INTO `demo_property` (`spid`, `pid`, `selling`) VALUES
    (1, 3, 'Old House Farm'),
    (2, 3, 'The Willows'),
    (3, 3, 'Tall Trees'),
    (4, 3, 'The Melksham Florist'),
    (5, 2, 'Tall Trees'),
    (6, 2, 'The Melksham Florist'),
    (7, 1, 'Dun Roamin'),
    (9, 16, 'Noida Ex'),
    (10, 14, 'Yamuna Express Way'),
    (8, 1, 'Dun Roamin');

    mysql> use rnd
    Database changed
    
    mysql> SELECT * FROM `demo_people`;
    +-----+------------------+----------------+
    | pid | name             | phone          |
    +-----+------------------+----------------+
    |   1 | vikas dwivedi    | 09711500404    |
    |   2 | Ravi Shankar     | 98262626222    |
    |   3 | hemant Sharma    | 29293947474747 |
    |   4 | Madhulika sharma | 9835635353353  |
    |   5 | Vishal Dwivedi   | 9363636465475  |
    |   6 | Prakash Dwivedi  | 92846464463    |
    +-----+------------------+----------------+
    6 rows in set (0.00 sec)

    mysql> SELECT * FROM `demo_property`;
    +------+-----+----------------------+
    | spid | pid | selling              |
    +------+-----+----------------------+
    |    1 |   3 | Old House Farm       |
    |    2 |   3 | The Willows          |
    |    3 |   3 | Tall Trees           |
    |    4 |   3 | The Melksham Florist |
    |    5 |   2 | Tall Trees           |
    |    6 |   2 | The Melksham Florist |
    |    7 |   1 | Dun Roamin           |
    |    8 |   1 | Dun Roamin           |
    +------+-----+----------------------+
    8 rows in set (0.01 sec)
    
###################### INNER JOIN ##############################
mysql> SELECT a.pid, a.name, a.phone, b.selling
    -> FROM demo_people a, demo_property b
    -> WHERE a.pid = b.pid;
+-----+---------------+----------------+----------------------
| pid | name          | phone          | selling
+-----+---------------+----------------+----------------------
|   1 | vikas dwivedi | 09711500404    | Dun Roamin
|   1 | vikas dwivedi | 09711500404    | Dun Roamin
|   2 | Ravi Shankar  | 98262626222    | Tall Trees
|   2 | Ravi Shankar  | 98262626222    | The Melksham Florist
|   3 | hemant Sharma | 29293947474747 | Old House Farm
|   3 | hemant Sharma | 29293947474747 | The Willows
|   3 | hemant Sharma | 29293947474747 | Tall Trees
|   3 | hemant Sharma | 29293947474747 | The Melksham Florist
+-----+---------------+----------------+----------------------
8 rows in set (0.00 sec)
###################### LEFT JOIN ##############################
    mysql> SELECT a.pid,a.name, a.phone, b.selling
    -> FROM demo_people a LEFT JOIN `demo_property` b
    -> ON a.pid = b.pid;
+-----+------------------+----------------+-------------------
| pid | name             | phone          | selling
+-----+------------------+----------------+-------------------
|   1 | vikas dwivedi    | 09711500404    | Dun Roamin
|   1 | vikas dwivedi    | 09711500404    | Dun Roamin
|   2 | Ravi Shankar     | 98262626222    | Tall Trees
|   2 | Ravi Shankar     | 98262626222    | The Melksham Flori
|   3 | hemant Sharma    | 29293947474747 | Old House Farm
|   3 | hemant Sharma    | 29293947474747 | The Willows
|   3 | hemant Sharma    | 29293947474747 | Tall Trees
|   3 | hemant Sharma    | 29293947474747 | The Melksham Flori
|   4 | Madhulika sharma | 9835635353353  | NULL
|   5 | Vishal Dwivedi   | 9363636465475  | NULL
|   6 | Prakash Dwivedi  | 92846464463    | NULL
+-----+------------------+----------------+-------------------
11 rows in set (0.30 sec)
###################### RIGHT JOIN ##############################
    mysql> SELECT a.pid,a.name, a.phone, b.selling
    -> FROM demo_people a RIGHT JOIN `demo_property` b
    -> ON a.pid = b.pid;
+-----+------------------+----------------+-------------------
| pid | name             | phone          | selling
+-----+------------------+----------------+-------------------
|   1 | vikas dwivedi    | 09711500404    | Dun Roamin
|   1 | vikas dwivedi    | 09711500404    | Dun Roamin
|   2 | Ravi Shankar     | 98262626222    | Tall Trees
|   2 | Ravi Shankar     | 98262626222    | The Melksham Flori
|   3 | hemant Sharma    | 29293947474747 | Old House Farm
|   3 | hemant Sharma    | 29293947474747 | The Willows
|   3 | hemant Sharma    | 29293947474747 | Tall Trees
|   3 | hemant Sharma    | 29293947474747 | The Melksham Flori
|   4 | Madhulika sharma | 9835635353353  | NULL
|   5 | Vishal Dwivedi   | 9363636465475  | NULL
|   6 | Prakash Dwivedi  | 92846464463    | NULL
+-----+------------------+----------------+-------------------
11 rows in set (0.30 sec)
#################################################################
###################### SELF JOIN #############################
## Select records from "demo_property" 
## where "spid" and "pid" are equal

    mysql> SELECT a.spid,a.pid, a.selling
    -> FROM `demo_property` a , `demo_property` b
    -> WHERE a.spid = b.pid;
################# CROSS JOIN################
mysql> select * from demo_people,demo_property;

mysql> SELECT MAX(spid) FROM demo_property;
+-----------+
| MAX(spid) |
+-----------+
|        10 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT MAX(spid) as maximum FROM demo_property;
+---------+
| maximum |
+---------+
|      10 |
+---------+
#################  SECOND LARGEST CATEGORY/Salary ###############
mysql> SELECT MAX(spid) as maximum FROM demo_property WHERE spid < (SELECT MAX(spid) as maximum FROM demo_property );
+---------+
| maximum |
+---------+
|       9 |
+---------+
1 row in set (0.29 sec)

mysql> SELECT * FROM `demo_property`as e 
WHERE 3=(select count(spid) from demo_property where e.spid<=spid);

+------+-----+------------+
| spid | pid | selling    |
+------+-----+------------+
|    8 |   1 | Dun Roamin |
+------+-----+------------+
1 row in set (0.00 sec)


############# 3rd Largest with LIMIT ##########
mysql> SELECT * FROM `demo_property` ORDER BY `spid` DESC
    -> LIMIT 2,1;
+------+-----+------------+
| spid | pid | selling    |
+------+-----+------------+
|    8 |   1 | Dun Roamin |
+------+-----+------------+
1 row in set (0.00 sec)


########### SELECT SELLINGS START FROM AS
mysql> SELECT * FROM `demo_property`
    -> WHERE `selling` REGEXP '^[A-S]';
+------+-----+----------------+
| spid | pid | selling        |
+------+-----+----------------+
|    1 |   3 | Old House Farm |
|    7 |   1 | Dun Roamin     |
|    8 |   1 | Dun Roamin     |
|    9 |  16 | Noida Ex       |
+------+-----+----------------+
4 rows in set (0.00 sec)

mysql> SELECT *     FROM `categories`     WHERE `cat_name` NOT     REGEXP '^[a-z]';
+----+-----------+----------+
| id | parent_id | cat_name |
+----+-----------+----------+
| 44 |        28 | 1234     |
| 43 |        28 | 123      |
| 42 |        28 | 12       |
| 39 |        44 | 121212   |
| 38 |        35 | 222222   |
| 37 |        35 | 1111     |
+----+-----------+----------+
6 rows in set (0.00 sec)

mysql> SELECT *     FROM `categories`     WHERE `cat_name` REGEXP '[^a-z]';
+----+-----------+-------------+
| id | parent_id | cat_name    |
+----+-----------+-------------+
| 44 |        28 | 1234        |
| 43 |        28 | 123         |
| 42 |        28 | 12          |
| 41 |        28 | wer 222     |
| 40 |        28 | wer 1       |
| 35 |         0 | New Sub Cat |
| 34 |         0 | New Sub Cat |
| 33 |         0 | sub caat    |
| 39 |        44 | 121212      |
| 38 |        35 | 222222      |
| 37 |        35 | 1111        |
+----+-----------+-------------+
11 rows in set (0.00 sec)

mysql> SELECT *     FROM `categories`     WHERE `cat_name` REGEXP '[^ a-z]';
+----+-----------+----------+
| id | parent_id | cat_name |
+----+-----------+----------+
| 44 |        28 | 1234     |
| 43 |        28 | 123      |
| 42 |        28 | 12       |
| 41 |        28 | wer 222  |
| 40 |        28 | wer 1    |
| 39 |        44 | 121212   |
| 38 |        35 | 222222   |
| 37 |        35 | 1111     |
+----+-----------+----------+
8 rows in set (0.00 sec)

mysql> SELECT *     FROM `categories`     WHERE `cat_name` REGEXP '^[^ a-z]';
+----+-----------+----------+
| id | parent_id | cat_name |
+----+-----------+----------+
| 44 |        28 | 1234     |
| 43 |        28 | 123      |
| 42 |        28 | 12       |
| 39 |        44 | 121212   |
| 38 |        35 | 222222   |
| 37 |        35 | 1111     |
+----+-----------+----------+
6 rows in set (0.00 sec)
